package com.aaa.sbms.dao;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface LeaveDao {

    /**
     * 查询部门
     * @return
     */
    @Select("select * from depts where deptid!=5")
    List<Map> depts();

    /**
     * 分页查询人事审核
     * @param map
     * @return
     */
    @Select("<script>" +
            "select ll.id,u.userid,u.username,d.dname,ll.phone,u.sex,p.pname,u.idcard,ll.filepath\n" +
            "from leavelist ll\n" +
            "left join users u on u.userid=ll.userid\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "<where>" +
            "ll.firstcheck=0\n" +
            "<if test=\"deptid!=null and deptid!=''\">and d.deptid=#{deptid}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where>"+
            "order by ll.id desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> leavePage(Map map);

    /**
     * 查询人事审核总数量
     * @param map
     * @return
     */
    @Select("<script>" +
            "select count(*)\n" +
            "from leavelist ll\n" +
            "left join users u on u.userid=ll.userid\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "<where>" +
            "ll.firstcheck=0\n" +
            "<if test=\"deptid!=null and deptid!=''\">and d.deptid=#{deptid}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where>" +
            "</script>")
    int leavePageCount(Map map);

    /**
     * 分页查询领导审核
     * @param map
     * @return
     */
    @Select("<script>" +
            "select ll.id,u.userid,u.username,d.dname,ll.phone,u.sex,p.pname,u.idcard,ll.filepath,ll.firstcheck,ll.reason,date_format(ll.applydate,'%Y-%m-%d %H:%i:%s') applydate\n" +
            "from leavelist ll\n" +
            "left join users u on u.userid=ll.userid\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "<where>" +
            "ll.firstcheck=2 and ll.secondcheck is null\n" +
            "<if test=\"deptid!=null and deptid!=''\">and d.deptid=#{deptid}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where>"+
            "order by ll.id desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> leaderCheckPage(Map map);

    /**
     * 查询领导审核总数量
     * @param map
     * @return
     */
    @Select("<script>" +
            "select count(*)\n" +
            "from leavelist ll\n" +
            "left join users u on u.userid=ll.userid\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "<where>" +
            "ll.firstcheck=2\n" +
            "<if test=\"deptid!=null and deptid!=''\">and d.deptid=#{deptid}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where>" +
            "</script>")
    int leaderCheckPageCount(Map map);

    /**
     * 查询请假审核记录
     * @param map
     * @return
     */
    @Select("<script>" +
            "select ll.id,u.userid,u.username,u.sex,u.idcard,ll.phone,d.dname,ll.firstcheck,ll.secondcheck," +
            "date_format(ll.begintime,'%Y-%m-%d %H:%i:%s') begintime,date_format(ll.endtime,'%Y-%m-%d %H:%i:%s') endtime," +
            "date_format(ll.applydate,'%Y-%m-%d %H:%i:%s') applydate\n" +
            "from leavelist ll\n" +
            "left join users u on u.userid=ll.userid\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "<where>" +
            "ll.firstcheck is not null\n" +
            "<if test=\"begintime!=null and begintime!=''\">and ll.begintime>#{begintime}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where>"+
            "order by ll.id desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> checkLeavePage(Map map);

    /**
     * 查询请假审核总数量
     * @param map
     * @return
     */
    @Select("<script>" +
            "select count(*)\n" +
            "from leavelist ll\n" +
            "left join users u on u.userid=ll.userid\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "<where>" +
            "ll.firstcheck is not null\n" +
            "<if test=\"begintime!=null and begintime!=''\">and ll.begintime>#{begintime}</if>" +
            "<if test='username!=null'>and u.username like '%${username}%'</if>" +
            "</where>" +
            "</script>")
    int checkLeavePageCount(Map map);

    /**
     * 查询个人请假信息
     * @param userid
     * @return
     */
    @Select("select id,l.userid,u.username,l.phone,leavetype,urge,begintime,endtime,applydate,l.filepath,reason from leavelist l\n" +
            "left join users u on u.userid=l.userid where l.userid=10000002 and firstcheck=1")
    Map leaveOne(int userid);

    @Select("select count(*) from leavelist where firstcheck=0 and userid=#{userid}")
    int isFirstCheckNo(int userId);

    /**
     * 查询一个人事审核
     * @param id
     * @return
     */
    @Select("select ll.id,u.userid,u.username,u.sex,u.idcard," +
            "d.dname,ll.phone,p.pname,ll.filepath,ll.reason\n" +
            "from leavelist ll\n" +
            "left join users u on u.userid=ll.userid\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "where id=#{id}")
    Map personCheckOne(int id);

    /**
     * 查询一个领导审核
     * @param id
     * @return
     */
    @Select("select ll.id,u.*,d.dname,ll.phone,p.pname,ll.filepath,ll.reason,ll.firstcheck,ll.reason,ll.suggest\n" +
            "from leavelist ll\n" +
            "left join users u on u.userid=ll.userid\n" +
            "left join depts d on d.deptid=u.deptid\n" +
            "left join positions p on p.pid=u.positionid\n" +
            "where id=#{id}")
    Map leaderCheckOne(int id);

    /**
     * 添加请假申请
     * @param map
     * @return
     */
    @Insert("insert into leavelist(id,userid,phone,leavetype,urge,leavedays,begintime,endtime,applydate,filepath,reason,firstcheck)\n" +
            "values(null,#{userid},#{phone},#{leavetype},#{urge},#{leavedays},#{begintime},#{endtime}," +
            "date_format(now(),'%Y-%m-%d %H:%i:%s'),#{filepath},#{reason},0)")
    int addLeave(Map map);

    /**
     *修改人事部审核结果
     * @param map
     * @return
     */
    @Update("update leavelist set firstcheck=#{firstcheck},suggest=#{suggest} where id=#{id}")
    int updateFirstCheck(Map map);

    /**
     * 修改领导审核结果
     * @param map
     * @return
     */
    @Update("update leavelist set secondcheck=#{secondcheck} where id=#{id}")
    int updateSecondCheck(Map map);
}
